package com.gack.business.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.gack.business.model.Position;
import com.gack.business.vo.PositionVO;

@Repository
public interface PositionRepository extends JpaRepository<Position, String>, JpaSpecificationExecutor<Position> {

	@Query("from Position where enterpriseId=:enterpriseId and status=1")
	List<Position> findByEnterpriseId(@Param("enterpriseId")String enterpriseId);//查询某公司下所有职位
	
	@Query(value = "select new com.gack.business.vo.PositionVO(p.id,"
			+ " p.name,"
			+ " (select id from JobRank where id=p.jobrankId),"
			+ " (select name from JobRank where id=p.jobrankId))"
			+ " from Position p ,JobRank jr"
			+ " where p.jobrankId = jr.id"
			+ " and p.enterpriseId=:enterpriseId"
			+ " and p.status=1"
			+ " order by jr.level ASC, p.updateTime ASC")
	List<PositionVO> findVOByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询未被删除职位VO
	
	@Query(value = "select new com.gack.business.vo.PositionVO(p.id,"
			+ " p.name,"
			+ " (select id from JobRank where id=p.jobrankId),"
			+ " (select name from JobRank where id=p.jobrankId))"
			+ " from Position p"
			+ " where p.jobrankId=:jobrankId"
			+ " and p.status=1"
			+ " order by p.updateTime ASC")
	List<PositionVO> findVOByJobRankId(@Param("jobrankId") String jobrankId);//查询未被删除职位VO
	
	@Query("select id"
			+ " from Position"
			+ " where status=1"
			+ " and jobrankId=:jobRankId")
	List<String> findIdByJobRankId(@Param("jobRankId") String jobRankId);//查询未被删除职位的id
	
	@Query("select count(p.id)"
			+ " from Position p"
			+ " where p.id=:positionId"
			+ " and p.enterpriseId=:enterpriseId"
			+ " and p.status=1")
	int countByEnterpriseIdAndPositionId(@Param("enterpriseId") String enterpriseId, @Param("positionId") String positionId);//查询某公司下某职位的数量(用来检测某公司和某职位是否有关联)
	
	@Query("select count(p.id)"
			+ " from Position p"
			+ " where p.enterpriseId=:enterpriseId"
			+ " and p.name=:positionName"
			+ " and p.status=1")
	int countSameName(@Param("enterpriseId") String enterpriseId, @Param("positionName") String positionName);//查询某公司下,重名个数
	
	@Query("select count(p.id)"
			+ " from Position p"
			+ " where p.enterpriseId=:enterpriseId"
			+ " and p.name=:positionName"
			+ " and p.id!=:positionId"
			+ " and p.status=1")
	int countSameNameExcludeMe(@Param("enterpriseId") String enterpriseId, @Param("positionId") String positionId, @Param("positionName") String positionName);//查询某公司下,重名个数,不包含自己
	
	@Query("select p.name"
			+ " from Position p"
			+ " where p.id=:positionId")
	String findNameById(@Param("positionId") String positionId);//根据id获取职位名称
	
	@Modifying
	@Query("update Position"
			+ " set status=:status"
			+ " where id=:positionId") 
	int updateStatus(@Param("positionId")String positionId, @Param("status")Integer status);
	
	@Modifying
	@Query("update Position"
			+ " set status=0,"
			+ " deleteTime=:date"
			+ " where enterpriseId=:enterpriseId")
	int deleteByEnterpriseId(@Param("enterpriseId")String enterpriseId, @Param("date")Date date);
	
	@Modifying
	@Query("update Position"
			+ " set jobrankId=:jobRankId,"
			+ " updateTime=:date"
			+ " where enterpriseId=:enterpriseId"
			+ " and status=1")
	int updateJobRankIdAndUpdateTimeByEnterpriseId(@Param("enterpriseId") String enterpriseId, @Param("jobRankId") String jobRankId, @Param("date") Date date);
	
	@Modifying
	@Query("update Position"
			+ " set jobrankId=:jobRankId,"
			+ " deleteTime=:date"
			+ " where enterpriseId=:enterpriseId"
			+ " and status=0")
	int updateJobRankIdAndDeleteTimeByEnterpriseId(@Param("enterpriseId") String enterpriseId, @Param("jobRankId") String jobRankId, @Param("date") Date date);

}


